package cn.weiyuanbiao.DbGeneralCore.util;

import cn.weiyuanbiao.DbGeneralCore.entity.ClassFieldEntity;
import cn.weiyuanbiao.DbGeneralCore.entity.ClassTableEntity;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DbConnUtil {
    private static Connection conn = null;
    private static String driverClass = PropsUtils.getValue("jdbc.driverClass");
    private static String url = PropsUtils.getValue("jdbc.url");
    private static String username = PropsUtils.getValue("jdbc.username");
    private static String userpwd = PropsUtils.getValue("jdbc.password");
    private static String schema_name = PropsUtils.getValue("jdbc.schema_name");

    private static String author = PropsUtils.getValue("version.author");
    private static String company = PropsUtils.getValue("version.company");
    private static String dateFormate = PropsUtils.getValue("version.dateFormate");

    static {
        conn = getConn();
    }

    /**
     * 取得连接
     *
     * @param url
     * @param username
     * @param userpwd
     * @return
     */
    public static Connection getConn(String url, String username, String userpwd) {
        Connection conn = null;
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException ex) {
            System.out.println("jdbc 驱动程序不存在");
            System.exit(0);
        }
        try {
            conn = DriverManager.getConnection(url, username, userpwd);
        } catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("连接数据库失败" + ex.getMessage() + "\n\n" + ex.getStackTrace());
            System.exit(0);
        }
        return conn;
    }

    /**
     * 取得系统默认的连接
     *
     * @return
     */
    public static Connection getConn() {
        if (conn == null)
            conn = getConn(url, username, userpwd);
        return conn;
    }

    /**
     * 关闭部分资源方法
     */
    public static void closeAll(Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                System.out.println("RS关闭发生异常");
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                System.out.println("pstmt关闭发生异常");
                e.printStackTrace();
            }
        }
    }

    public static ClassTableEntity getTable(String tableName) {
        ClassTableEntity cte = new ClassTableEntity();
        cte.setTableName(tableName);
        cte.setClassfields(getClassFiled(tableName));
        cte.setPKfields(getPKClassFiled(tableName));
        cte.setTableNote(getTableNote(tableName));
        cte.setAuthor(author);
        cte.setCompany(company);
        cte.setCreateTime(dateFormate);
        return cte;
    }

    public static String getTableNote(String tableName) {
        Connection conn = getConn();
        String result = "";
        String sql = "";
        sql = " select `TABLE_COMMENT` from `TABLES` where `TABLE_NAME` = '" + tableName + "'";
        Statement sta = null;
        ResultSet rs = null;
        try {
            sta = conn.createStatement();
            rs = sta.executeQuery(sql);
            while (rs.next()) {
                result = rs.getString("TABLE_COMMENT");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(sta, rs);
        }
        return result;
    }

    private static void handle(List<ClassFieldEntity> cfeList, ResultSet rs, Statement sta, String sql) {
        try {
            sta = conn.createStatement();
            rs = sta.executeQuery(sql);
            while (rs.next()) {
                ClassFieldEntity cfe = new ClassFieldEntity();
                cfe.setFieldName(rs.getString("COLUMN_NAME"));
                cfe.setFieldNote(rs.getString("COLUMN_COMMENT"));
                cfe.setFieldType(rs.getString("DATA_TYPE"));
                cfeList.add(cfe);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(sta, rs);
        }
    }

    public static List<ClassFieldEntity> getClassFiled(String tableName) {
        String sql = " select `COLUMN_NAME`,`DATA_TYPE`,`COLUMN_COMMENT` from `COLUMNS` "
                + "where `TABLE_NAME` = '" + tableName + "' "
                + "and `TABLE_SCHEMA` = '" + schema_name + "'";
        Statement sta = null;
        ResultSet rs = null;
        List<ClassFieldEntity> cfeList = new ArrayList<ClassFieldEntity>();
        handle(cfeList, rs, sta, sql);
        return cfeList;
    }

    /**
     * 获得主键
     */
    public static List<ClassFieldEntity> getPKClassFiled(String tableName) {
        String sql = " SELECT distinct k.COLUMN_NAME,h.`DATA_TYPE`,h.`COLUMN_COMMENT` "
                + " FROM information_schema.table_constraints t "
                + " JOIN information_schema.key_column_usage k "
                + " USING (constraint_name,table_schema,table_name) "
                + " left join information_schema.COLUMNS h "
                + " on h.column_name = k.column_name  and h.TABLE_NAME = k.table_name "
                + " WHERE t.constraint_type='PRIMARY KEY' "
                + "   AND t.table_schema='" + schema_name + "' "
                + "   AND t.table_name='" + tableName + "'";
        Statement sta = null;
        ResultSet rs = null;
        List<ClassFieldEntity> cfeList = new ArrayList<ClassFieldEntity>();
        handle(cfeList, rs, sta, sql);
        return cfeList;
    }

    /**
     * 获取所有table名字
     *
     * @return
     */
    public static List<String> getAllTableName() {
        return getAllTableName(schema_name);
    }

    /**
     * 获取所有table名字
     *
     * @param schema
     * @return
     */
    public static List<String> getAllTableName(String schema) {
        Connection conn = getConn();
        String result = "";
        String sql = "";
        sql = " select `TABLE_NAME` from `TABLES` where  `TABLE_SCHEMA` = '" + schema + "'";
        Statement sta = null;
        ResultSet rs = null;
        List<String> tableNames = new ArrayList<String>();
        try {
            sta = conn.createStatement();
            rs = sta.executeQuery(sql);
            while (rs.next()) {
                result = rs.getString("TABLE_NAME");
                tableNames.add(result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(sta, rs);
        }
        return tableNames;
    }


    public static void main(String[] args) throws IOException {
        List<ClassFieldEntity> l = DbConnUtil.getPKClassFiled("ConfigShoppingGoodsPeriod");
        for (ClassFieldEntity classFieldEntity : l) {
            System.out.println(classFieldEntity.getFieldName());
        }
    }

}
